Share via

Cant choose Key for upsert, in Copy Data-activity

Axel 20 Reputation points
2024-05-10T12:05:43.3633333+00:00

I want to copy Data from an Azure SQL to a comsosdb container, with the copy data activity of Azure Synapse. As I cannot enter the uniqe key column, for the upsert behaviour, i receive a unique key violation error.

Why is there no option to select the key-column when copying into comosdb

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,484 questions
Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,470 questions
Accepted answer
  1. BhargavaGunnam-MSFT 27,661 Reputation points Microsoft Employee
    2024-05-10T20:27:33.2966667+00:00

    Hello Axel,

    Welcome to the Microsoft Q&A forum.

    Azure Synapse's Copy Data activity does not provide an option to specify the unique key column for upsert operations into Azure Cosmos DB.

    The reason behind this is:

    The id field in Cosmos DB item acts as the key column to perform update or delete or upsert operations. When performing update or delete actions, specifying the partition key is mandatory. This is because the service uses the partition key to search for the id field value and perform the update or delete operation accordingly.

    The unique key violation error you're encountering suggests that the data being copied contains duplicate values for the id field defined in your Cosmos DB container.

    Please ensure that the data in Azure SQL does not contain duplicates for the fields that form the unique key in Cosmos DB.

    I hope this answers your question.

    Please let me know if you have any further questions.

    1 person found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest